import java.sql.*;
import java.util.Scanner;

/**
 * Created by JoseMiguel on 11/02/14.
 */
public class Practica02_Main {

    public static void main (String[] args)
    {
        int currentID;
        try {
            Class.forName("org.sqlite.JDBC");
            Connection conn = DriverManager.getConnection("jdbc:sqlite:test.sqlite");
            java.sql.Statement statement = conn.createStatement();
            ResultSet resultset = statement.executeQuery("select * from country;");

            while (resultset.next())
                System.out.println(resultset.getString("name"));

            resultset = statement.executeQuery("select max(id) from people");
            currentID = Integer.parseInt(resultset.getString(1));
            insertarPersona (statement, ++currentID);

            resultset = statement.executeQuery("select * from people");
            while(resultset.next()) {
                System.out.print(resultset.getString("id") + " " +
                        resultset.getString("name") + " " +
                        resultset.getString("occupation") + " ");
                        String idPais = resultset.getString("born");

                java.sql.Statement statePais = conn.createStatement();
                ResultSet resultsetPais = statePais.executeQuery("select name from country where id = " + idPais + ";");
                System.out.println(resultsetPais.getString(1));

            }

            statement.close();
            conn.close();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void insertarPersona (Statement statement, int currentID) throws SQLException {
        Scanner sc = new Scanner(System.in);
        System.out.print("Inserta nombre : ");
        String name = sc.nextLine();
        System.out.println();

        System.out.print("Inserta Ocupacion : ");
        String occupation = sc.nextLine();
        System.out.println();

        System.out.print("Inserta Pais : ");
        String country = sc.nextLine();
        System.out.println();

        ResultSet resultset = statement.executeQuery("select * from country where name='" + country + "'");
        resultset.next();
        int cID = resultset.getInt("id");

        statement.executeUpdate("insert into people values ('" + name + "', '" + occupation + "', " + currentID + ", " + cID + ");");
    }
}
